﻿///***************************************************************
/// 시  스  템 : Updms.Cfg
/// 단위시스템 : CFG
/// 계      층 : Presentation Layer
/// 프로그램명 : Cfg1013m
/// 파  일  명 : Cfg1013m.aspx.cs
/// 설      명 : 소스생성기
///***************************************************************
/// 수정ID 일자       이름     내용
///---------------------------------------------------------------
/// CH001  2008-11-28 송시명   최초 작성
///***************************************************************
using System;
using System.Data;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

using SMFramework.Data;
using SMFramework.Utility;

using Base.AppFramework.Web.UI.CommandBar;
using Base.Dac;

using Updms.Cfg.Exe;


namespace Updms.Cfg
{
	public partial class Cfg1013m : Updms.Common.BaseClass.PageBase
	{
		#region 전역영역

		//프로그램상수
		public static readonly string PID = "Cfg1013m";
		public static readonly string gs_path_info = "/updms/Cfg/" + PID + ".aspx";

		Global global = new Global();
		CommCode.Cfg cfg = new CommCode.Cfg(LANG_SET);

		//검색
		protected string kw1 = "";	// 데이터베이스
		protected string kw2 = "";	// 테이블

		//메뉴 선택값
		protected string gs_sm = "";

		// 페이지모드
		private string gs_page_mode = "";
		
		//소스 생성시 사용
		private static string gs_div_line = "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━";

		#endregion

		#region Page_Load, OnPageLoad

		protected void Page_Load(object sender, EventArgs e)
		{
			SetInit();
			if (!Page.IsPostBack)
			{
				// 최초 화면 로딩시 처리해야 할 부분
				OnPageLoad();
			}
		}

		// OnPageLoad
		private void OnPageLoad()
		{
			SetLiterals(global.GetLabelInfoByPgmId(PID, LANG_SET));
			SetClientScript();
			SetDatabseInfo();
			SetTableInfo("");
			SetSourceDiv();
			SetSearchParameter();
		}

		#endregion

		#region InitializeComponent

		private void InitializeComponent()
		{
			this.Load += new System.EventHandler(this.Page_Load);
			this.PreRender += new System.EventHandler(this.Page_PreRender);
		}

		#endregion

		#region SetClientScript

		private void SetClientScript()
		{

		}

		#endregion

		#region SetInit, SetSearchParameter, ShowKeywordLabel

		//초기값설정
		private void SetInit()
		{
			lbl_search_flag.Text = "INIT";
			
			gs_sm = (Request.QueryString["sm"] == null || Request.QueryString["sm"].Trim().Length != 7)
					? global.GetDirDepth(PID) : Request.QueryString["sm"].ToString();
		}

		//검색파라미터
		private void SetSearchParameter()
		{
			// Source
		}	

		#endregion

		#region 마스터 Method (SetDatabseInfo, SetTableInfo)

		//데이터베이스 조회
		private void SetDatabseInfo()
		{
			DataSet lo_ds = new DataSet();		

			using (Cfg1013m_NT _Cfg1013m_NT = new Cfg1013m_NT())
			{
				lo_ds.Merge(_Cfg1013m_NT.GetDatabaseInfo());

				ddl_search_database.Items.Clear();
				ddl_search_database.DataSource = lo_ds;
				ddl_search_database.DataMember = "UPDMS_OBJECT";
				ddl_search_database.DataTextField = "DB";
				ddl_search_database.DataValueField = "DB";
				ddl_search_database.DataBind();
				ddl_search_database.Items.Insert(0, new ListItem(DDL_SELECT, ""));
			}
		}

		//데이터베이스 조회
		private void SetTableInfo(string ps_database)
		{
			DataSet lo_ds = new DataSet();
			DataPack lo_dp = new DataPack();
			ddl_search_table.Items.Clear();

			if (String.IsNullOrEmpty(ps_database))
			{				
				ddl_search_table.Items.Insert(0, new ListItem(DDL_SELECT, ""));
			}
			else
			{
				using (Cfg1013m_NT _Cfg1013m_NT = new Cfg1013m_NT())
				{
					lo_dp.AddProperty("ls_database", typeof(string), ps_database);					
					lo_ds.Merge(_Cfg1013m_NT.GetTableInfo(lo_dp));

					ddl_search_table.DataSource = lo_ds;
					ddl_search_table.DataMember = "UPDMS_OBJECT";
					ddl_search_table.DataTextField = "TABLE_NAME";
					ddl_search_table.DataValueField = "TABLE_NAME";
					ddl_search_table.DataBind();
				}
			}
		}

		//데이터베이스 변경시
		protected void ddl_search_database_SelectedIndexChanged(object sender, EventArgs e)
		{
			SetTableInfo(ddl_search_database.SelectedValue);
		}

		#endregion

		#region 디테일 Method (SetSourceDiv)

		//소스구분
		private void SetSourceDiv()
		{
			cfg.SetSourceDiv(ddl_div);
		}

		#endregion

		#region 디테일 프로시져생성

		#region 유틸리티함수

		//파일명
		public static string GetSpFilePrefix(string ls_sp_nm)
		{
			string ls_script_file = "";	// 스크립트 파일 형식으로 생성

			//스크립트파일
			ls_script_file =
				"\r\n\r\n\r\n" + gs_div_line + "\r\n" +
				"* 파일명 : dbo." + ls_sp_nm + ".sql\r\n" + gs_div_line + "\r\n" +
				"IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' " + "AND name = '" + ls_sp_nm + "')\r\n" +
				"	BEGIN\r\n" +
				"		DROP Procedure " + ls_sp_nm + "\r\n" +
				"	END\r\n" +
				"GO\r\n" +
				"\r\n" +
				"SET ANSI_NULLS ON\r\n" +
				"SET QUOTED_IDENTIFIER ON\r\n" +
				"GO\r\n";

			return ls_script_file;
		}
		
		//데이터타입 검사하여 길이설정을 할지말지
		private  bool IsSetLength(string ps_data_type)
		{
			if (ps_data_type == "datetime" || 
				ps_data_type == "decimal" ||
				ps_data_type == "int" || 
				ps_data_type == "int identity" ||
				ps_data_type == "money")
			{
				return false;
			}
			else
			{
				return true;
			}
		}

		//최초등록일, 최종수정일 여부
		private bool IsCreateUpdateDt(string ps_field_nm, string ps_data_type)
		{
			if (ps_field_nm == "Create_Dt" && ps_data_type == "datetime" || 
				ps_field_nm == "Update_Dt" && ps_data_type == "datetime")
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		//마지막 ps_value 삭제
		private string SubStr(string ps_data, string ps_value)
		{
			string ls_rslt = "";
			if (ps_data.Contains(ps_value))
			{
				ls_rslt = ps_data.Substring(0, ps_data.LastIndexOf(ps_value));
			}
			else
			{
				ls_rslt = ps_data;
			}
			
			return ls_rslt;
		}
		
		#endregion		

		#region 프로시져 생성 - 검색결과수
		
		//프로시져 생성 - 검색결과수
		private string SetSPGetSearchCount()
		{
			string ls_table		= ddl_search_table.SelectedValue;	//테이블명
			string ls_pgm_div	= txt_pgm_div.Text;					//프로그램구분
			string ls_pgm_id	= txt_pgm_id.Text;					//프로그램아이디
			string ls_comment	= txt_comment.Text;					//설명
			string ls_search_param = "";							//검색파라미터
			
			StringBuilder sb = new StringBuilder();
			
			//검색
			string ls_sp_nm = "usp_" + ls_table + "_Get_Search_Count";
			
			sb.Append(GetSpFilePrefix(ls_sp_nm));
			sb.Append("/******************************************************************************\r\n");
			sb.Append("**\tName : " + ls_sp_nm + "\r\n");
			sb.Append("**\tDesc : " + ls_comment + "의 검색결과수를 카운트한다.\r\n");
			sb.Append("**\tTest Exec Query : Exec " + ls_sp_nm + "\r\n");
			sb.Append("**\tCalled by : " + ls_pgm_div + "_Dac_" + ls_table + ".cs\r\n");
			sb.Append("**\tProgram ID : " + ls_pgm_id + "\r\n");
			sb.Append("**\tAuth : 송시명\r\n");
			sb.Append("**\tDate : " + DateUtil.GetNow("yyyy-MM-dd") + "\r\n");
			sb.Append("*******************************************************************************\r\n");
			sb.Append("**\tChange History\r\n");
			sb.Append("*******************************************************************************\r\n");
			sb.Append("**\tDate:\t\tAuthor:\t\tDescription:\r\n");
			sb.Append("**\t--------\t--------\t---------------------------------------\r\n");
			sb.Append("**\t\r\n");
			sb.Append("*******************************************************************************/\r\n");
			sb.Append("CREATE PROC [dbo].[" + ls_sp_nm + "]\r\n");
			
			//검색어 설정			
			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				GridViewRow row = GridView1.Rows[i];
				HtmlInputCheckBox cb_search = (HtmlInputCheckBox)row.FindControl("cb_search");

				if (cb_search.Checked)
				{
					string ls_column	= GridView1.Rows[i].Cells[2].Text;	//컬럼명
					string ls_data_type = GridView1.Rows[i].Cells[4].Text;	//데이터타입
					string ls_langth	= GridView1.Rows[i].Cells[5].Text;	//길이

					if (Convert.ToInt32(ls_langth) > 8000)
					{
						ls_data_type = ls_data_type.Substring(0, 1) == "n" ? "nvarchar" : "varchar";
						ls_langth = "max";
					}
					
					//데이터타입 처리
					if (IsSetLength(ls_data_type))
					{
						ls_search_param += "@ls_" + ls_column.ToLower() + " " + ls_data_type + "(" + ls_langth + "),\r\n";
					}
					else
					{
						ls_search_param += "@ls_" + ls_column.ToLower() + " " + ls_data_type + ",\r\n";
					}
				}
			}

			//마지막 쉼표 삭제
			sb.Append(SubStr(ls_search_param, ","));
						
			sb.Append("\r\nAS\r\n");
			sb.Append("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED\r\n");
			sb.Append("SET NOCOUNT ON\r\n");
			sb.Append("\r\n");
			sb.Append("SELECT COUNT(Seq) AS Search_Count\r\n");
			sb.Append("  FROM " + ls_table + " WITH(NOLOCK)\r\n");
			
			//조건절 설정		
			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				GridViewRow row = GridView1.Rows[i];
				HtmlInputCheckBox cb_search = (HtmlInputCheckBox)row.FindControl("cb_search");

				if (cb_search.Checked)
				{
					string ls_column	= GridView1.Rows[i].Cells[2].Text;	//컬럼명
					string ls_data_type = GridView1.Rows[i].Cells[4].Text;	//데이터타입
					string ls_langth	= GridView1.Rows[i].Cells[5].Text;	//길이
					string ls_prefix = "";									//키워드
					string ls_where = "";									//조건절

					//하나면 where 그다음은 and
					ls_prefix = (i == 1) ? " WHERE " : "   AND ";

					ls_where += ls_prefix + ls_column + " LIKE '%' + @ls_" + ls_column.ToLower() + " + '%'\r\n";

					sb.Append(ls_where);
				}
			}

			sb.Append("\r\nGO\r\n");
			sb.Append(gs_div_line + "\r\n");
			
			return sb.ToString();
		}

		#endregion

		#region 프로시져 생성 - 조회

		//프로시져 생성 - 조회
		private string SetSPSelectPaging()
		{
			string ls_table		= ddl_search_table.SelectedValue;	//테이블명
			string ls_pgm_div	= txt_pgm_div.Text;					//프로그램구분
			string ls_pgm_id	= txt_pgm_id.Text;					//프로그램아이디
			string ls_comment	= txt_comment.Text;					//설명
			string ls_search_param	= "";							//검색파라미터
			string ls_field			= "";							//필드
			string ls_sub_field		= "";							//서브쿼리 필드
			string ls_order_by		= "";							//정렬

			StringBuilder sb = new StringBuilder();

			//검색
			string ls_sp_nm = "usp_" + ls_table + "_Select_Paging";

			sb.Append(GetSpFilePrefix(ls_sp_nm));
			sb.Append("/******************************************************************************\r\n");
			sb.Append("**\tName : " + ls_sp_nm + "\r\n");
			sb.Append("**\tDesc : " + ls_comment + "을 조회한다.\r\n");
			sb.Append("**\tTest Exec Query : Exec " + ls_sp_nm + "\r\n");
			sb.Append("**\tCalled by : " + ls_pgm_div + "_Dac_" + ls_table + ".cs\r\n");
			sb.Append("**\tProgram ID : " + ls_pgm_id + "\r\n");
			sb.Append("**\tAuth : 송시명\r\n");
			sb.Append("**\tDate : " + DateUtil.GetNow("yyyy-MM-dd") + "\r\n");
			sb.Append("*******************************************************************************\r\n");
			sb.Append("**\tChange History\r\n");
			sb.Append("*******************************************************************************\r\n");
			sb.Append("**\tDate:\t\tAuthor:\t\tDescription:\r\n");
			sb.Append("**\t--------\t--------\t---------------------------------------\r\n");
			sb.Append("**\t\r\n");
			sb.Append("*******************************************************************************/\r\n");
			sb.Append("CREATE PROC [dbo].[" + ls_sp_nm + "]\r\n");
			
			//검색어 설정
			sb.Append("@li_st_page int,\r\n");
			sb.Append("@li_ed_page int,\r\n");
			
			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				GridViewRow row = GridView1.Rows[i];
				HtmlInputCheckBox cb = (HtmlInputCheckBox)row.FindControl("cb_search");

				if (cb.Checked)
				{
					string ls_column	= GridView1.Rows[i].Cells[2].Text;	//컬럼명
					string ls_data_type = GridView1.Rows[i].Cells[4].Text;	//데이터타입
					string ls_langth	= GridView1.Rows[i].Cells[5].Text;	//길이

					if (Convert.ToInt32(ls_langth) > 8000)
					{
						ls_data_type = ls_data_type.Substring(0, 1) == "n" ? "nvarchar" : "varchar";
						ls_langth = "max";
					}
					
					//데이터타입 처리
					if (IsSetLength(ls_data_type))
					{
						ls_search_param += "@ls_" + ls_column.ToLower() + " " + ls_data_type + "(" + ls_langth + "),\r\n";
					}
					else
					{
						ls_search_param += "@ls_" + ls_column.ToLower() + " " + ls_data_type + ",\r\n";
					}
				}
			}

			//마지막 쉼표 삭제
			sb.Append(SubStr(ls_search_param, ","));

			sb.Append("\r\nAS\r\n");
			sb.Append("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED\r\n");
			sb.Append("SET NOCOUNT ON\r\n");
			sb.Append("\r\n");
			sb.Append("SELECT \r\n");

			//필드
			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				GridViewRow row = GridView1.Rows[i];
				HtmlInputCheckBox cb_check = (HtmlInputCheckBox)row.FindControl("cb_check");

				if (cb_check.Checked)
				{
					string ls_column	= GridView1.Rows[i].Cells[2].Text;	//컬럼명
					string ls_data_type = GridView1.Rows[i].Cells[4].Text;	//데이터타입
					string ls_langth	= GridView1.Rows[i].Cells[5].Text;	//길이
					ls_field += "       a." + ls_column + ",\r\n";
				}
			}

			//마지막 쉼표 삭제
			sb.Append(SubStr(ls_field, ","));
			
			sb.Append("\r\n  FROM (\r\n");

			//정렬설정
			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				GridViewRow row = GridView1.Rows[i];
				HtmlInputCheckBox cb_order_by = (HtmlInputCheckBox)row.FindControl("cb_order_by");

				if (cb_order_by.Checked)
				{
					string ls_column = GridView1.Rows[i].Cells[2].Text;	//컬럼명
					ls_order_by += ls_column + ", ";
				}
			}

			sb.Append("       SELECT ROW_NUMBER() OVER(ORDER BY " + SubStr(ls_order_by, ",") + " ASC) AS Rnum,\r\n");
			
			//서브쿼리 필드설정
			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				GridViewRow row = GridView1.Rows[i];
				HtmlInputCheckBox cb = (HtmlInputCheckBox)row.FindControl("cb_check");

				if (cb.Checked)
				{
					string ls_column	= GridView1.Rows[i].Cells[2].Text;	//컬럼명
					string ls_data_type = GridView1.Rows[i].Cells[4].Text;	//데이터타입
					string ls_langth	= GridView1.Rows[i].Cells[5].Text;	//길이
					ls_sub_field += "              " + ls_column + ",\r\n";
				}
			}

			sb.Append(SubStr(ls_sub_field, ","));
			sb.Append("\r\n         FROM " + ls_table + " WITH(NOLOCK)\r\n");

			//조건절 설정		
			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				GridViewRow row = GridView1.Rows[i];
				HtmlInputCheckBox cb_check = (HtmlInputCheckBox)row.FindControl("cb_search");

				if (cb_check.Checked)
				{
					string ls_column	= GridView1.Rows[i].Cells[2].Text;	//컬럼명
					string ls_data_type = GridView1.Rows[i].Cells[4].Text;	//데이터타입
					string ls_langth	= GridView1.Rows[i].Cells[5].Text;	//길이
					string ls_prefix	= "";									//키워드
					string ls_where		= "";									//조건절

					//하나면 where 그다음은 and
					ls_prefix = (i == 1) ? "        WHERE " : "          AND ";

					ls_where += ls_prefix + ls_column + " LIKE '%' + @ls_" + ls_column.ToLower() + " + '%'\r\n";

					sb.Append(ls_where);
				}
			}

			sb.Append("       ) a\r\n");
			sb.Append(" WHERE a.Rnum > @li_st_page AND a.Rnum <= @li_ed_page\r\n");
			sb.Append("\r\n");
			sb.Append("GO\r\n");
			sb.Append(gs_div_line+"\r\n");
			

			return sb.ToString();
		}

		#endregion

		#region 프로시져 생성 - 저장

		//프로시져 생성 - 저장
		private string SetSPInsert()
		{
			string ls_table		= ddl_search_table.SelectedValue;	//테이블명
			string ls_pgm_div	= txt_pgm_div.Text;					//프로그램구분
			string ls_pgm_id	= txt_pgm_id.Text;					//프로그램아이디
			string ls_comment	= txt_comment.Text;					//설명
			string ls_insert_param	= "";							//저장파라미터
			string ls_insert_field	= "";							//저장필드
			string ls_insert_data	= "";							//저장값

			StringBuilder sb = new StringBuilder();

			//검색
			string ls_sp_nm = "usp_" + ls_table + "_Insert";

			sb.Append(GetSpFilePrefix(ls_sp_nm));
			sb.Append("/******************************************************************************\r\n");
			sb.Append("**\tName : " + ls_sp_nm + "\r\n");
			sb.Append("**\tDesc : " + ls_comment + "을 저장한다.\r\n");
			sb.Append("**\tTest Exec Query : Exec " + ls_sp_nm + "\r\n");
			sb.Append("**\tCalled by : " + ls_pgm_div + "_Dac_" + ls_table + ".cs\r\n");
			sb.Append("**\tProgram ID : " + ls_pgm_id + "\r\n");
			sb.Append("**\tAuth : 송시명\r\n");
			sb.Append("**\tDate : " + DateUtil.GetNow("yyyy-MM-dd") + "\r\n");
			sb.Append("*******************************************************************************\r\n");
			sb.Append("**\tChange History\r\n");
			sb.Append("*******************************************************************************\r\n");
			sb.Append("**\tDate:\t\tAuthor:\t\tDescription:\r\n");
			sb.Append("**\t--------\t--------\t---------------------------------------\r\n");
			sb.Append("**\t\r\n");
			sb.Append("*******************************************************************************/\r\n");
			sb.Append("CREATE PROC [dbo].[" + ls_sp_nm + "]\r\n");			
			
			//파라미터설정
			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				string ls_column	= GridView1.Rows[i].Cells[2].Text;	//컬럼명
				string ls_data_type = GridView1.Rows[i].Cells[4].Text;	//데이터타입
				string ls_langth	= GridView1.Rows[i].Cells[5].Text;	//길이

				if (Convert.ToInt32(ls_langth) > 8000)
				{
					ls_data_type = ls_data_type.Substring(0, 1) == "n" ? "nvarchar" : "varchar";
					ls_langth = "max";
				}

				//데이터타입 처리
				if (IsSetLength(ls_data_type))
				{
					ls_insert_param += "@" + ls_column + " " + ls_data_type + "(" + ls_langth + "),\r\n";
				}
				else
				{
					ls_insert_param += "@" + ls_column + " " + ls_data_type.Replace(" identity", "") + ",\r\n";
				}
			}

			//마지막 쉼표 삭제
			sb.Append(SubStr(ls_insert_param, ","));

			sb.Append("\r\nAS\r\n");
			sb.Append("SET NOCOUNT OFF\r\n");
			sb.Append("\r\n");
			sb.Append("INSERT INTO " + ls_table + " (\r\n");
			
			//저장컬럼 설정
			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				string ls_column	= GridView1.Rows[i].Cells[2].Text;	//컬럼명
				string ls_data_type = GridView1.Rows[i].Cells[4].Text;	//데이터타입
					
				//Seq는 제외
				if (!ls_data_type.Contains("int identity"))
				{
					ls_insert_field += "       " + ls_column + ",\r\n";
				}
			}

			sb.Append(SubStr(ls_insert_field, ","));
			sb.Append("\r\n)\r\nVALUES (\r\n");
			
			//저장값설정
			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				string ls_column	= GridView1.Rows[i].Cells[2].Text;	//컬럼명
				string ls_data_type = GridView1.Rows[i].Cells[4].Text;	//데이터타입
					
				//Seq는 제외
				if (!ls_data_type.Contains("int identity"))
				{
					//datetime처리
					if (ls_data_type.Contains("datetime"))
					{
						ls_insert_data += "       GETDATE(),\r\n";
					}
					else
					{
						ls_insert_data += "       @" + ls_column + ",\r\n";
					}
				}
			}

			//마지막 쉼표 삭제
			sb.Append(SubStr(ls_insert_data, ","));

			sb.Append("\r\n)\r\n\r\nGO\r\n");
			sb.Append(gs_div_line + "\r\n");

			return sb.ToString();
		}

		#endregion

		#region 프로시져 생성 - 수정

		//프로시져 생성 - 수정
		private string SetSPUpdate()
		{
			string ls_table			= ddl_search_table.SelectedValue;	//테이블명
			string ls_pgm_div		= txt_pgm_div.Text;					//프로그램구분
			string ls_pgm_id		= txt_pgm_id.Text;					//프로그램아이디
			string ls_comment		= txt_comment.Text;					//설명
			string ls_update_param	= "";								//수정파라미터
			string ls_update_field	= "";								//저장필드

			StringBuilder sb = new StringBuilder();

			string ls_sp_nm = "usp_" + ls_table + "_Update";

			sb.Append(GetSpFilePrefix(ls_sp_nm));
			sb.Append("/******************************************************************************\r\n");
			sb.Append("**\tName : " + ls_sp_nm + "\r\n");
			sb.Append("**\tDesc : " + ls_comment + "을 수정한다.\r\n");
			sb.Append("**\tTest Exec Query : Exec " + ls_sp_nm + "\r\n");
			sb.Append("**\tCalled by : " + ls_pgm_div + "_Dac_" + ls_table + ".cs\r\n");
			sb.Append("**\tProgram ID : " + ls_pgm_id + "\r\n");
			sb.Append("**\tAuth : 송시명\r\n");
			sb.Append("**\tDate : " + DateUtil.GetNow("yyyy-MM-dd") + "\r\n");
			sb.Append("*******************************************************************************\r\n");
			sb.Append("**\tChange History\r\n");
			sb.Append("*******************************************************************************\r\n");
			sb.Append("**\tDate:\t\tAuthor:\t\tDescription:\r\n");
			sb.Append("**\t--------\t--------\t---------------------------------------\r\n");
			sb.Append("**\t\r\n");
			sb.Append("*******************************************************************************/\r\n");
			sb.Append("CREATE PROC [dbo].[" + ls_sp_nm + "]\r\n");

			//파라미터설정
			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				string ls_column	= GridView1.Rows[i].Cells[2].Text;	//컬럼명
				string ls_data_type = GridView1.Rows[i].Cells[4].Text;	//데이터타입
				string ls_langth	= GridView1.Rows[i].Cells[5].Text;	//길이

				if (Convert.ToInt32(ls_langth) > 8000)
				{
					ls_data_type = ls_data_type.Substring(0, 1) == "n" ? "nvarchar" : "varchar";
					ls_langth = "max";
				}

				//데이터타입 처리
				if (IsSetLength(ls_data_type))
				{
					ls_update_param += "@" + ls_column + " " + ls_data_type + "(" + ls_langth + "),\r\n";
				}
				else
				{
					ls_update_param += "@" + ls_column + " " + ls_data_type.Replace(" identity", "") + ",\r\n";
				}
			}

			//마지막 쉼표 삭제
			sb.Append(SubStr(ls_update_param, ","));

			sb.Append("\r\nAS\r\n");
			sb.Append("SET NOCOUNT OFF\r\n");
			sb.Append("\r\n");
			sb.Append("UPDATE " + ls_table + "\r\n   SET\r\n");

			//수정컬럼 설정
			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				string ls_column	= GridView1.Rows[i].Cells[2].Text;	//컬럼명
				string ls_data_type = GridView1.Rows[i].Cells[4].Text;	//데이터타입

				//Seq는 제외
				if (!ls_data_type.Contains("int identity"))
				{
					//수정은 Create_User, Create_Dt를 업데이트 하지 않는다
					if (ls_column == "Create_User" || ls_column == "Create_Dt")
					{
					}
					else
					{
						//datetime처리
						if (IsCreateUpdateDt(ls_column, ls_data_type))
						{
							ls_update_field += "       " + ls_column + " = GETDATE(),\r\n";
						}
						else
						{
							ls_update_field += "       " + ls_column + " = @" + ls_column + ",\r\n";
						}
					}
				}
			}

			//마지막 쉼표 삭제
			sb.Append(SubStr(ls_update_field, ","));
			
			//수정조건설정(Seq)
			sb.Append("\r\n WHERE Seq = @Seq\r\n");
			sb.Append("\r\nGO\r\n");
			sb.Append(gs_div_line + "\r\n");

			return sb.ToString();
		}

		#endregion

		#region 프로시져 생성 - 삭제

		//프로시져 생성 - 삭제
		private string SetSPDelete()
		{
			string ls_table		= ddl_search_table.SelectedValue;	//테이블명
			string ls_pgm_div	= txt_pgm_div.Text;					//프로그램구분
			string ls_pgm_id	= txt_pgm_id.Text;					//프로그램아이디
			string ls_comment	= txt_comment.Text;					//설명

			StringBuilder sb = new StringBuilder();

			string ls_sp_nm = "usp_" + ls_table + "_Delete";

			sb.Append(GetSpFilePrefix(ls_sp_nm));
			sb.Append("/******************************************************************************\r\n");
			sb.Append("**\tName : " + ls_sp_nm + "\r\n");
			sb.Append("**\tDesc : " + ls_comment + "을 삭제한다.\r\n");
			sb.Append("**\tTest Exec Query : Exec " + ls_sp_nm + "\r\n");
			sb.Append("**\tCalled by : " + ls_pgm_div + "_Dac_" + ls_table + ".cs\r\n");
			sb.Append("**\tProgram ID : " + ls_pgm_id + "\r\n");
			sb.Append("**\tAuth : 송시명\r\n");
			sb.Append("**\tDate : " + DateUtil.GetNow("yyyy-MM-dd") + "\r\n");
			sb.Append("*******************************************************************************\r\n");
			sb.Append("**\tChange History\r\n");
			sb.Append("*******************************************************************************\r\n");
			sb.Append("**\tDate:\t\tAuthor:\t\tDescription:\r\n");
			sb.Append("**\t--------\t--------\t---------------------------------------\r\n");
			sb.Append("**\t\r\n");
			sb.Append("*******************************************************************************/\r\n");
			sb.Append("CREATE PROC [dbo].[" + ls_sp_nm + "]\r\n");

			//파라미터설정
			sb.Append("@ls_xml nvarchar(max) = '<UPDMS></UPDMS>' --루트노드\r\n");
			sb.Append("AS\r\n\r\n");
			sb.Append("BEGIN\r\n");
			sb.Append("\r\n");
			sb.Append("DECLARE @iXmlHandle int\r\n");
			sb.Append("EXEC sp_xml_preparedocument @iXmlHandle OUTPUT, @ls_xml\r\n\r\n");
			sb.Append("DELETE FROM " + ls_table + "\r\n");
			sb.Append(" WHERE Seq IN (\r\n");
			sb.Append("         SELECT Seq\r\n");
			sb.Append("           FROM OPENXML(@iXmlHandle, '/UPDMS/" + ls_table + "', 2)\r\n");
			sb.Append("           WITH ( Seq int ) x\r\n");
			sb.Append("       )\r\n");
			sb.Append("\r\n");
			sb.Append("--메모리 해제\r\n");
			sb.Append("EXEC sp_xml_removedocument @iXmlHandle\r\n\r\n");
			sb.Append("END\r\n\r\n");
			sb.Append("GO\r\n");
			sb.Append(gs_div_line + "\r\n");

			return sb.ToString();
		}

		#endregion		

		#endregion

		#region 디테일 코드비하인드

		#region 비하인드 코드 - ClearValue

		//비하인드 코드 - ClearValue
		private string SetAspxCsClearValue()
		{
			string ls_control = "";
			StringBuilder sb = new StringBuilder();

			sb.Append("\r\n\r\n" + gs_div_line + "\r\n");
			sb.Append("* ClearValue\r\n");
			sb.Append(gs_div_line + "\r\n");
			sb.Append("lbl_pk.Text = \"\";\r\n");
			sb.Append("h_no.Value = \"\";\r\n");

			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				GridViewRow row = GridView1.Rows[i];
				HtmlInputCheckBox cb_check = (HtmlInputCheckBox)row.FindControl("cb_check");

				if (cb_check.Checked)
				{
					HtmlInputCheckBox cb_drop_down_list = (HtmlInputCheckBox)row.FindControl("cb_drop_down_list");
					HtmlInputCheckBox cb_radio_button_list = (HtmlInputCheckBox)row.FindControl("cb_radio_button_list");

					string ls_column = GridView1.Rows[i].Cells[2].Text;	//컬럼명
					string ls_comment = GridView1.Rows[i].Cells[3].Text;	//설명

					//드랍다운리스트나 라디오버튼리스트이면
					if (cb_drop_down_list.Checked)
					{
						ls_control += "ddl_" + ls_column.ToLower() + ".SelectedIndex = 0;\t//" + ls_comment + "\r\n";
					}
					else if (cb_radio_button_list.Checked)
					{
						ls_control += "rbl_" + ls_column.ToLower() + ".SelectedIndex = 0;\t//" + ls_comment + "\r\n";
					}
					else
					{
						ls_control += "txt_" + ls_column.ToLower() + ".Text = \"\";\t//" + ls_comment + "\r\n";
					}
				}
			}

			sb.Append(ls_control + "\r\n");
			sb.Append(gs_div_line + "\r\n");

			return sb.ToString();
		}
		
		#endregion
		
		#region 비하인드 코드 - SetValue

		//비하인드 코드 - SetValue
		private string SetAspxCsSetValue()
		{
			string ls_control = "";
			StringBuilder sb = new StringBuilder();

			sb.Append("\r\n\r\n" + gs_div_line + "\r\n");
			sb.Append("* SetValue\r\n");
			sb.Append(gs_div_line + "\r\n");
			sb.Append("DataPack lo_dp = new DataPack();\r\n");

			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				GridViewRow row = GridView1.Rows[i];
				HtmlInputCheckBox cb_hidden_field = (HtmlInputCheckBox)row.FindControl("cb_hidden_field");
				HtmlInputCheckBox cb_drop_down_list = (HtmlInputCheckBox)row.FindControl("cb_drop_down_list");
				HtmlInputCheckBox cb_radio_button_list = (HtmlInputCheckBox)row.FindControl("cb_radio_button_list");

				string ls_column = GridView1.Rows[i].Cells[2].Text;	//컬럼명
				string ls_comment = GridView1.Rows[i].Cells[3].Text;	//설명

				string ls_prefix = "lo_dp.AddProperty(\"";
				string ls_typeof = "typeof(string)";

				//드랍다운리스트
				if (cb_drop_down_list.Checked)
				{
					ls_control += ls_prefix + ls_column + "\", " + ls_typeof + ", ddl_" + ls_column.ToLower() + ".SelectedValue);\t//" + ls_comment + "\r\n";
				}
				//라디오버튼리스트
				else if (cb_radio_button_list.Checked)
				{
					ls_control += ls_prefix + ls_column + "\", " + ls_typeof + ", rbl_" + ls_column.ToLower() + ".SelectedValue);\t//" + ls_comment + "\r\n";
				}
				else
				{
					ls_control += ls_prefix + ls_column + "\", " + ls_typeof + ", ConversionUtil.DefaultValue(txt_" + ls_column.ToLower() + ".Text));\t//" + ls_comment + "\r\n";
				}
			}

			ls_control = ls_control.Replace("ConversionUtil.DefaultValue(txt_seq.Text));", "lbl_pk.Text);").ToString().Replace("ConversionUtil.DefaultValue(txt_attach_file1.Text));", "lbl_attach_file1.Text.Replace(\"&nbsp;\",\"\");		//첨부파일").ToString().Replace("ConversionUtil.DefaultValue(txt_attach_file1_size.Text));", "lbl_attach_file1_size.Text.Replace(\"&nbsp;\",\"\");	//첨부파일사이즈").ToString().Replace("ConversionUtil.DefaultValue(txt_create_user.Text)", "USER_ID").ToString().Replace("ConversionUtil.DefaultValue(txt_update_user.Text)", "USER_ID").ToString().Replace("ConversionUtil.DefaultValue(txt_create_dt.Text)", "\"\"").ToString().Replace("ConversionUtil.DefaultValue(txt_update_dt.Text)", "\"\"");

			sb.Append(ls_control + "\r\n");
			sb.Append("\r\nreturn lo_dp;\r\n");
			sb.Append(gs_div_line + "\r\n");

			return sb.ToString();
		}
		
		#endregion

		#region 비하인드 코드 - GridView1_RowCommand

		//비하인드 코드 - GridView1_RowCommand
		private string SetAspxCsRowCommand()
		{
			string ls_control_value = "";
			string ls_file_attach = "";							//첨부파일
			StringBuilder sb = new StringBuilder();
			int ls_file_attach_cnt = 0;		//첨부파일 체크여부 확인용

			sb.Append("\r\n\r\n" + gs_div_line + "\r\n");
			sb.Append("* GridView1_RowCommand\r\n");
			sb.Append(gs_div_line + "\r\n");

			sb.Append("GridView gv = (GridView)sender;\r\n");
			sb.Append("GridViewRow row = (GridViewRow)(((Control)e.CommandSource).NamingContainer);\r\n");
			sb.Append("\r\n");
			sb.Append("LinkButton lbtn_rc = (LinkButton)GridView1.Rows[row.RowIndex].FindControl(\"lbtn_rc\");\r\n");
			sb.Append("\r\n");
			sb.Append("//row.Cells[0].Text.ToString();\t//0.체크박스\r\n");
			sb.Append("h_no.Value = row.Cells[1].Text.ToString();\t//1.번호\r\n");
			sb.Append("lbl_pk.Text = gv.DataKeys[row.RowIndex].Values[\"Seq\"].ToString();\t//2.Seq\r\n");

			for (int i = 0; i < GridView1.Rows.Count; i++)
			{
				GridViewRow row = GridView1.Rows[i];
				HtmlInputCheckBox cb_check = (HtmlInputCheckBox)row.FindControl("cb_check");
				HtmlInputCheckBox cb_template = (HtmlInputCheckBox)row.FindControl("cb_template");
				HtmlInputCheckBox cb_hidden_field = (HtmlInputCheckBox)row.FindControl("cb_hidden_field");
				HtmlInputCheckBox cb_drop_down_list = (HtmlInputCheckBox)row.FindControl("cb_drop_down_list");
				HtmlInputCheckBox cb_radio_button_list = (HtmlInputCheckBox)row.FindControl("cb_radio_button_list");
				HtmlInputCheckBox cb_attach_file = (HtmlInputCheckBox)row.FindControl("cb_attach_file");

				string ls_column = GridView1.Rows[i].Cells[2].Text;	//컬럼명
				string ls_f_idx = (i + 2).ToString();				//필드의 인덱스 값
				string ls_control = "";								//콘트롤
				string ls_value = "";								//값
				
				if (cb_check.Checked)
				{
					//콘트롤
					//드랍다운리스트
					if (cb_drop_down_list.Checked)
					{
						ls_control = "ddl_" + ls_column.ToLower() + ".SelectedValue";
					}
					//라디오버튼리스트
					else if (cb_radio_button_list.Checked)
					{
						ls_control = "rbl_" + ls_column.ToLower() + ".SelectedValue";
					}
					else
					{
						ls_control = "txt_" + ls_column.ToLower() + ".Text";
					}
					
					//값
					//숨김필드이면 값을 DataKeys에서 가져온다
					if (cb_hidden_field.Checked)
					{
						ls_value = "= gv.DataKeys[row.RowIndex].Values[\"" + ls_column + "\"].ToString();";
					}
					else
					{
						ls_value = "= row.Cells[" + ls_f_idx + "].Text.ToString();";
					}

					//주석
					if (cb_template.Checked)
					{
						//템플릿필드이면
						ls_control_value += ConversionUtil.AddTab(ls_control) + "= lbtn_rc.Text;" + "\t//" + ls_f_idx + "." + ls_column.ToString() + "(template)\r\n";
					}
					else
					{
						//첨부파일
						if (cb_attach_file.Checked)
						{
							ls_file_attach_cnt++;

							//첨부파일사이즈
							if (ls_column.ToLower().Contains("size"))
							{
								ls_control_value += ConversionUtil.AddTab("string ls_" + ls_column.ToLower() + "_size") + "= gv.DataKeys[row.RowIndex].Values[\"" + ls_column + "\"].ToString();\t//" + ls_f_idx + "." + ls_column.ToString() + "\r\n";
								
							}
							else
							{
								ls_control_value += ConversionUtil.AddTab("string ls_" + ls_column.ToLower()) + "= gv.DataKeys[row.RowIndex].Values[\"" + ls_column + "\"].ToString();\t//" + ls_f_idx + "." + ls_column.ToString() + "\r\n";
							}							
						}
						else
						{
							ls_control_value += ConversionUtil.AddTab(ls_control) + ls_value + "\t//" + ls_f_idx + "." + ls_column.ToString() + "\r\n";
						}
					}
				}
			}

			sb.Append(ls_control_value + "\r\n");

			//첨부파일 체크되어 있으면 실행
			if (ls_file_attach_cnt > 0)
			{
				ls_file_attach = "//첨부파일1\r\n";
				ls_file_attach += "if (!String.IsNullOrEmpty(ls_attach_file1))\r\n";
				ls_file_attach += "{\r\n";
				ls_file_attach += "lbl_attach_file1.Text = ls_attach_file1;\r\n";
				ls_file_attach += "lbl_attach_file1_size.Text = ls_attach_file1_size;\r\n";
				ls_file_attach += "ibtn_delete_attach_file1.Visible = true;\r\n";
				ls_file_attach += "\r\n";
				ls_file_attach += "}\r\n";
				ls_file_attach += "else\r\n";
				ls_file_attach += "{\r\n";
				ls_file_attach += "lbl_attach_file1.Text = \"\";\r\n";
				ls_file_attach += "lbl_attach_file1_size.Text = \"\";\r\n";
				ls_file_attach += "ibtn_delete_attach_file1.Visible = false;\r\n";
				ls_file_attach += "}\r\n";

				sb.Append(ls_file_attach + "\r\n");
			}
			sb.Append(gs_div_line + "\r\n");

			return sb.ToString();
		}
		
		#endregion

		#endregion

		#region CRUD (GetSpColumnsEx, SearchProcess)

		//조회
		private void GetSpColumnsEx()
		{
			DataSet lo_ds = new DataSet();
			DataPack lo_dp = new DataPack();

			using (Cfg1013m_NT _Cfg1013m_NT = new Cfg1013m_NT())
			{
				lo_dp.AddProperty("ls_table", typeof(string), ddl_search_table.SelectedValue);
				lo_ds.Merge(_Cfg1013m_NT.GetSpColumnsEx(lo_dp));
			}

			GridView1.DataSource = lo_ds;
			GridView1.DataBind();
		}
		
		//검색
		private void SearchProcess()
		{
			lbl_search_flag.Text = "SEARCH";
			SetSearchParameter();
			ClearValue();
			GetSpColumnsEx();
		}

		#endregion

		#region SetValue, ClearValue

		//SetValue
		private DataPack SetValue()
		{
			DataPack lo_dp = new DataPack();

			return lo_dp;
		}

		//ClearValue
		private void ClearValue()
		{

		}

		#endregion

		#region Page_PreRender, ChangePageMode

		// Page_PreRender
		private void Page_PreRender(object sender, System.EventArgs e)
		{
			if (!Page.IsPostBack)
			{
				using (AccessCheck access = new AccessCheck())
				{
					//접근권한 체크
					if (!access.CheckPgmAccessGrant(PID, USER_ID, ACCESS_LOG_INFO))
					{
						Response.Redirect(URL_NO_AUTHORITY_REDIRECT_BASE);	//사용자의 권한은 요청하신 페이지를 볼 수 없습니다
					}
					//사용권한별 버튼처리
					PagePreRender.SetVisibleCtrl(this, access.CheckPgmUsingGrant(USER_DIV, PID), access.GetVisibleCtrl(PID));
				}
			}
			ChangePageMode();
		}

		// ChangePageMode
		private void ChangePageMode()
		{
			switch (gs_page_mode)
			{
				case "":	//처음 페이지로드시
					div_toggle.Visible = false;
					table_list_set.Visible = false;
					table_detail_1.Visible = false;
					table_detail_2.Visible = false;
					break;
				case "R":	//검색버튼 클릭시
					div_toggle.Visible = true;
					table_list_set.Visible = true;
					table_detail_1.Visible = true;
					table_detail_2.Visible = false;
					break;
				case "U":	//생성버튼 클릭시
					div_toggle.Visible = true;
					table_list_set.Visible = true;
					table_detail_1.Visible = true;
					table_detail_2.Visible = true;
					break;
			}
			SetGridViewHeader(global.GetColumnListByPgmId(PID, LANG_SET));
		}

		#endregion

		#region GridView이벤트

		//RowDataBound
		protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
		{
			if (e.Row.RowType == DataControlRowType.DataRow)
			{
				//마우스올렸을 때
				e.Row.Attributes.Add("onMouseOver", GRID_ONMOUSEOVER_COLOR);
				e.Row.Attributes.Add("onMouseOut", GRID_ONMOUSEOUT_COLOR);

				//번호
				e.Row.Cells[1].Text = (e.Row.RowIndex + 1).ToString();
			}

			//멀티헤더설정
			//SortedList lo_sl = new SortedList();
			//SMGridView sGv = new SMGridView();

			//lo_sl.Add("1", ",1,1");
			//lo_sl.Add("2", "데이터베이스정보,5,1");
			//lo_sl.Add("3", "조회쿼리용,2,1");
			//lo_sl.Add("4", "비하인드코드용,2,1");
			//sGv.SetMultiHeader(e, lo_sl, "multi_header");
		}		

		#endregion

		#region 버튼이벤트

		//검색
		protected void ibtn_search_Click(object sender, EventArgs e)
		{
			gs_page_mode = "R";
			SearchProcess();
		}

		//생성
		protected void ibtn_create_Click(object sender, EventArgs e)
		{
			gs_page_mode = "U";
			StringBuilder sb = new StringBuilder();
			
			switch (ddl_div.SelectedValue)
			{
				case "AB001":	//스토어드프로시져
					sb.Append(SetSPGetSearchCount());
					sb.Append(SetSPSelectPaging());
					sb.Append(SetSPInsert());
					sb.Append(SetSPUpdate());
					sb.Append(SetSPDelete());
					break;
				case "AB002":	//코드비하인드
					sb.Append(SetAspxCsClearValue());
					sb.Append(SetAspxCsSetValue());
					sb.Append(SetAspxCsRowCommand());
					break;
			}

			txt_source.Text = sb.ToString();
		}

		#endregion
		
	}
}
